Go Back   Forums @ The Digital Fix > Gadgets and Gizmos > Computing Forum

Notices

Reply
 
Thread Tools Display Modes
Old 13-11-2017, 12:52   #1
mnementh
Cheese is Evil
 
mnementh's Avatar
 
Join Date: Apr 2004
Location: In constant denial
Posts: 6,271
Thanks: 119
Thanked 55 Times in 41 Posts
Any SQL people here ?

Hi

This is beyond my very limited SQL single table skills, so hoping someone can help

I've got 2 tables with multiple columns. Both tables have some columns in common and some unique to each table.

Each table has a number of records where assignment_group = "a"

I'm looking to select all the records from both tables where assignment_group = "a", however my very basic select statement returns a syntax error.

Select [columns I want or *] from table1, table2 where assignment_group = "a"

Any help would be greatly appreciated.

Thanks
mnementh is offline   Reply With Quote
Old 13-11-2017, 13:04   #2
Wooglie
Rurouni
 
Wooglie's Avatar
 
Join Date: May 2001
Location: Farnborough
Posts: 5,688
Thanks: 1,138
Thanked 859 Times in 373 Posts
I think you want:
where table1.assignment_group = "a" OR table2.assignment_group = "a"

You might also want to join the tables together depending if that's possible or not.
Wooglie is offline   Reply With Quote
Thanked once by:
mnementh (14-11-2017)
Old 13-11-2017, 13:43   #3
arowley
The Killer of Threads
 
Join Date: Mar 2001
Location: Stockport
Posts: 38
Thanks: 48
Thanked 5 Times in 4 Posts
You would need to be able to join the tables together to stop a cartesian join occurring.

select
from table_a,
table_b
where table_a.id = table_b.id
and (table_a.assignment_group = 'a'
or table_a.assignment_group = 'a')

or you could union the results from 2 queries

select
from table_a
where table_a.assignment_group = 'a'
UNION
select
from table_b
where table_b.assignment_group = 'a'

It's hard to give a definitive answer as I haven't seen the schema.

Cheers
arowley is offline   Reply With Quote
Thanked 2 times by:
driver8 (13-11-2017), mnementh (14-11-2017)
Old 13-11-2017, 13:43   #4
mnementh
Cheese is Evil
 
mnementh's Avatar
 
Join Date: Apr 2004
Location: In constant denial
Posts: 6,271
Thanks: 119
Thanked 55 Times in 41 Posts
@Wooglie
Will give that a go - thanks.

Not sure a join is possible unfortunately

@arowley
Thanks for this.

Unfortunately I don't have a common ID in each table to be able to join on. One table is a list of incidents resolved by a team, the other is a list of requests solved by the same team.

I'm trying to pull the data into PowerBI so I can report on the data from a single dataset, rather than having to have multiple pages pointing to different datasets.

Last edited by mnementh; 13-11-2017 at 13:46.
mnementh is offline   Reply With Quote
Old 13-11-2017, 14:16   #5
arowley
The Killer of Threads
 
Join Date: Mar 2001
Location: Stockport
Posts: 38
Thanks: 48
Thanked 5 Times in 4 Posts
You could pull it into a single dataset using union, you just need to have the same number of columns in both queries

e.g. table a has columns a,b,c,d
table b has columns b,d,e,f

select a, b, c, d, null as e, null as f
from table_a
where table_a.assignment_group = 'a'
UNION
select null as a, b, null as c, d, e, f
from table_b
where table_b.assignment_group = 'a'
arowley is offline   Reply With Quote
Thanked once by:
mnementh (14-11-2017)
Old 13-11-2017, 14:23   #6
mnementh
Cheese is Evil
 
mnementh's Avatar
 
Join Date: Apr 2004
Location: In constant denial
Posts: 6,271
Thanks: 119
Thanked 55 Times in 41 Posts
Quote:
Originally Posted by arowley View Post
You could pull it into a single dataset using union, you just need to have the same number of columns in both queries

e.g. table a has columns a,b,c,d
table b has columns b,d,e,f

select a, b, c, d, null as e, null as f
from table_a
where table_a.assignment_group = 'a'
UNION
select null as a, b, null as c, d, e, f
from table_b
where table_b.assignment_group = 'a'
Thanks

I think I'm going to have to spend some time data mapping
mnementh is offline   Reply With Quote
Old 14-11-2017, 16:17   #7
mnementh
Cheese is Evil
 
mnementh's Avatar
 
Join Date: Apr 2004
Location: In constant denial
Posts: 6,271
Thanks: 119
Thanked 55 Times in 41 Posts
@arowley - Just wanted to say many thanks - all up and running
mnementh is offline   Reply With Quote
Old 15-11-2017, 13:00   #8
arowley
The Killer of Threads
 
Join Date: Mar 2001
Location: Stockport
Posts: 38
Thanks: 48
Thanked 5 Times in 4 Posts
No worries, glad it is working.
arowley is offline   Reply With Quote
Old 15-11-2017, 15:25   #9
hxbro
Trusted User
 
Join Date: Apr 2001
Location: At a computer
Posts: 1,343
Thanks: 4
Thanked 44 Times in 41 Posts
this might have worked too, need some indexes on assignment_group for speed

select * from table_a inner join table_b on table_a.assignment_group=table_b.assignment_group
where table_a.assignment_group='a'
hxbro is offline   Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
People Like Us dunkrag Television Discussion 19 25-06-2010 06:35
People who have Sky Plus Guest 17385 Television Discussion 35 05-11-2003 21:54
What are people doing about ET? Deaks Film Discussion 49 29-10-2002 13:35

All times are GMT. The time now is 04:33.


Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Copyright ©2000 - 2018 Poisonous Monkey Ltd. Part of The Digital Fix Network