CODEWITHSUNDEEP
sqlsqlitejoin
Adam
Adam

Reputation: 2682

Joining Queries?

How can I join these 2 queries together.

SELECT * 
  FROM tbl_person 
 WHERE tbl_person.status_id = '+ status_dg.selectedItem.status_id +';

SELECT * 
  FROM tbl_settings 
 WHERE tbl_settings.status_id = '+ status_dg.selectedItem.status_id +';

I tried this but doesn't seem to work.

SELECT * 
  FROM tbl_person, tbl_settings 
 WHERE tbl_person.status_id = '+ status_dg.selectedItem.status_id +' 
    OR tbl_settings.status_id = '+ status_dg.selectedItem.status_id +';

Here's a bit more information about what I'm trying to do.

In both tables (tbl_person, tbl_settings) status_id is a foreign key. In my application a user has the ability to create and delete statuses. So this query I'm trying to write is for when a status is being deleted. Before the status is deleted I need to check both tbl_person and tbl_settings to see if the status being deleted exists in either table. If either of the tables have a match to the status being deleted I need to promote the user.

Hope this helps.

Upvotes: 0

Views: 159

Answers (4)

lelewin
lelewin

Reputation: 559

I am a little confused with your question because you used tbl_person and tbl_status firstly, then you tried to test by joining tbl_person and tbl_settings.
Which two tables do you want to join?
If you want to join tbl_person and tbl_settings, how about this. 

SELECT *  
FROM tbl_person JOIN tbl_setting  
ON tbl_person.status_id = tbl_settings.status_id  
WHERE tbl_person.status_id = '+status_dg.selectedItem.status_id+'

Upvotes: 1

user194076
user194076

Reputation: 9027

Do not know what you need probably union? 

SELECT * 
  FROM tbl_person 
 WHERE tbl_person.status_id = '+ status_dg.selectedItem.status_id +'
union
SELECT * 
  FROM tbl_status 
 WHERE tbl_status.status_id = '+ status_dg.selectedItem.status_id +'

1.You should AVOID using "*" to select all records. You should specify set of columns you want to retrieve.
2. This line: WHERE tbl_person.status_id = '+ status_dg.selectedItem.status_id +' looks like you're doing something totally wrong. You should avoid putting business logic in SQL

and yes, provide more info

Upvotes: 1

permawash
permawash

Reputation: 171

To join the tables, you need to define the joining fields, which in your case seems to be the status_id fields.

This might not work correctly, but if you look at the last line you'll need that in your query:

SELECT * 
FROM tbl_person, tbl_settings 
WHERE 
  ( tbl_person.status_id='+status_dg.selectedItem.status_id+' 
  OR tbl_settings.status_id='+status_dg.selectedItem.status_id+' )
  AND tbl_person.status_id = tbl_settings.status_id;

Upvotes: 0

Josh
Josh

Reputation: 8201

Without more information on your table schema, try:

SELECT * 
  FROM tbl_person, tbl_settings 
 WHERE tbl_person.status_id = tbl_settings.status_id 
       AND tbl_person.status_id = '+status_dg.selectedItem.status_id+'

This will join the 2 tables, tbl_person and tbl_status, on status_id, and only retrieve rows where status_id = '+status_dg.selectedItem.status_id+'.

Upvotes: 0

Related Questions