CODEWITHSUNDEEP
sqloracle-database
NeS
NeS

Reputation: 147

SQL help different value on the same field

I need help with a request. I've two tables : ADDRESS and CUSTOM I can link the two tables with ADDRESS.ID = CUSTOM.ADDRESSID And I want to select all the addresses where CUSTOM.NAME = 'NUMBER' and CUSTOM.VALUE = '5' But also, on these addresses, I want to display the field CUSTOM.VALUE, where CUSTOM.NAME = 'INFO'

Here is some exemples

ADDRESS :

 ID      NAME      STREET
 1       Paul     65 blue street
 2       John     50 red street

CUSTOM :

ID      IDADDRESS     NAME    VALUE
 1           1         NUMBER    5
 2           1         INFO      1st floor
 3           2         NUMBER    6
 4           2         INFO      no info

What I want in result is : Paul - 65 blue street - 1st floor

Can someone help me with this request please ? I tried :

SELECT * FROM ADDRESS, CUSTOM WHERE ADDRESS.ID=CUSTOM.ADDRESSID AND ((CUSTOM.NAME='NUMBER' AND CUSTOM.VALUE='5') OR CUSTOM.NAME='INFO')

Upvotes: 0

Views: 90

Answers (3)

David Faber
David Faber

Reputation: 12485

I would to the following (it doesn't appear you actually need the value of the "custom NUMBER" field):

SELECT a.id, a.name, a.street, c.value
  FROM address a LEFT JOIN custom c
    ON a.id = c.addressid
   AND c.name = 'INFO'
 WHERE EXISTS ( SELECT 1 FROM custom c1
                 WHERE c1.addressid = a.id
                   AND c1.name = 'NUMBER'
                   AND c1.value = '5' );

This will get all the rows in ADDRESS for which a corresponding row in CUSTOM exists where name = 'NUMBER' and value = '5'. It will also retrieve these rows even if there isn't a corresponding 'INFO' row (hence the LEFT JOIN instead of an INNER JOIN) ... in these cases a NULL will be returned for the custom value.

Upvotes: 0

Entalyan
Entalyan

Reputation: 931

The table 'CUSTOM' appears to hold 2 different types of data, NUMBER and INFO. This means you should essentially treat both types of data as different tables, and join each seperately. Your query would/could like this:

SELECT a.NAME 
,   a.STREET 
,   inf.VALUE
FROM ADDRESS a
JOIN CUSTOM num
ON a.ID      = num.IDADDRESS
AND num.NAME = 'NUMBER'
JOIN CUSTOM inf
ON a.ID         = inf.IDADDRESS
AND inf.NAME    = 'INFO'
WHERE num.VALUE = '5'

If you have the option, try making the table CUSTOM into 2 actual tables, each only containing a single type of data. This will make it clear to other developers what type of data is in the table, and how to use the tables correctly.

Upvotes: 0

jarlh
jarlh

Reputation: 44796

Try the following, joining CUSTOM twice

SELECT *
FROM ADDRESS, CUSTOM c1, CUSTOM c2
WHERE ADDRESS.ID = c1.ADDRESSID
 AND  c1.ADDRESSID = c2.ADDRESSID and c1.NAME <> c2.NAME

Maybe I removed some conditions still needed...

Upvotes: 1

Related Questions