0

Here, I wrote a query using joins, the relation is fine, but I wanted display multiple columns from different tables which I used in joins, when I am trying to include or select other table columns it is performing cross join. Getting confused how to do add those columns which matches the conditions in joins. Can anyone please help me on this

select distinct p.*,ps.*,pp.*,pd.VERIFICATION_SUBJECT_ID,pd.VALUE
from Players p 
join Players_Service ps on p.ID=ps.ID
join Players_Data pd on pd.VERIFICATION_SUBJECT_ID=ps.VERIFICATION_SUBJECT_ID
join Players_Property pp on pp.ID=ps.ID

Players table:

[{ID:1,NAME:'abc'}]

Players_Data table:

[{VERIFICATION_SUBJECT_ID:3, VALUE:'AB-TYS'},{VERIFICATION_SUBJECT_ID:4, VALUE:'AB-QE'}]

Players_Property:

[{ID:1,CITY:'XYZ',ID:2,CITY:'ABC'}]

Player_Service:

[{VERIFICATION_SUBJECT_ID:3,PLACE:'ABCDE'}]

Expected Output:

ID:1,
NAME:'abc',
VERIFICATION_SUBJECT_ID:3,
PLACE:'ABCDE',
CITY:'XYZ',
VALUE:'AB-TYS'
4
  • I wanted to select the all columns for one table, and 2 specified columns from another table, when I am trying to do that in data I am able to see repetative. I have to display the columns of different tables based on matching values in condition
    – Dee
    Jun 13, 2022 at 10:32
  • Added sample records and expected result
    – Dee
    Jun 13, 2022 at 10:48
  • No for understanding of data I have added in the form of json
    – Dee
    Jun 13, 2022 at 10:53
  • Yes, There is no relation between players, players_data and there is no another table to connect these tables. But I want to display players_data columns and players_property columns in result
    – Dee
    Jun 13, 2022 at 12:12

1 Answer 1

0

Instead of using ".*", spell out the columns you desire:

SELECT p.ID,
       p.NAME,
       pd.VERIFICATION_SUBJECT_ID,
       ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.