CODEWITHSUNDEEP
sqloracle-database
FYP
FYP

Reputation: 39

sql queries to find the match attributes

i am currently doing a system like bartering system. the situation like this . A customer (Jasmine) require to input the 'NAME' attribute (what she have to seek) and input the'SEEK' attribute (what she need to seek). In order to get the result, the 'SEEK' attribute must match with 'Name' attribute from other customer and the 'SEEK' attribute from other customer must match with 'HAVE' attribute from Jasmine.

for example i have table items (already in database)

|ITEMSID|NAME   |SEEK     |USERID|
|A01    |printer|laptop   |A1    |
|A45    |laptop |headphone|A2    |
|AY3    |laptop |headphone|A3    |

For example Jasmine has 'Name' attribute headphone and 'Seek' attribute laptop and the result should like this. The result should list out all possibility (it may consists more than one row depends, and it may get zero result if the are no match betwen 'NAME' AND 'SEEK' attribute 

|ITEMSID|NAME   |SEEK     |USERID|
|AY3    |laptop |headphone|A3    |
|A45    |laptop |headphone|A2    |

Thank you for helping me. The system that i'm being develop using jsp p/s: i'm a new user in stackoverflow.

EDIT based on comments:

I already tried using an inner join but the query result is not specific on one transaction (mean the result match between 'Have' and 'Seek' attribute, but has all the matching rows and not the choose the 'Have' attribute that being input):

SELECT a.NAME, a.seek,a.ITEMSID 
FROM items a 
JOIN items b ON a.NAME = b.seek AND b.NAME = a.seek

Upvotes: 3

Views: 1063

Answers (1)

Galz
Galz

Reputation: 6842

Based on the query you have tried, if I understand your request correctly (not sure I do), all you are missing is the WHERE clause for the input.

SELECT a.name, a.seek, a.ITEMSID 
FROM items a 
JOIN items b 
  ON a.name = b.seek 
  AND b.name = a.seek
WHERE a.name = 'input here'

Upvotes: 1

Related Questions