8

SELECT specific rows linked to another table

 3 years ago
source link: https://www.codesd.com/item/select-specific-rows-linked-to-another-table.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

SELECT specific rows linked to another table

advertisements

I would like to pick up the data from Company table

The easiest way is like this

"Select id from Company "

Company Table
id company
1   comA
2   comB
3   comC

However I have another table Member Table which is connected with Company Table via company_id

Member Table
id  company_id companymember
1   1          john
2   1          Tam
3   2          haya
4   1          lee
5   3          kih
6   3          wild

Now I would like to pick "companys which have more than two members"

(In this case comA and comC has two members)

How can I make it by SQL??

You can use group by and having

select company, count(*)
from Company a
inner join member b on a._id = b.company_id
group by company
having count(*) > 2

Related Articles

Selecting specific rows from the DELETED table in a trigger

I have an intermediate table that defines many-to-many relationship between, for instance, Customer and Orders, like this: USE [master] GO CREATE DATABASE Example GO USE [Example] GO CREATE TABLE [dbo].[CustomerOrders]( [CustomerId] [int], [OrderId]

How to select specific rows in a SQL table without looping .net

Suppose I have a table called "Names" ID | Name ---+-------------- 1 | Bob 2 | Billy 3 | James 4 | John 5 | Tom and on and on with thousands of names... Suppose that I have written the following .net code to retrieve the names: public DataSet se

Create a table from an old one by selecting specific rows

how can I create a new table selecting specific rows of the old table ? The selected 3 rows have field1 = 243, 245 and 248 respectively. Also I need to select the rows with field1 > 0 from table3. thanksYou can use CREATE TABLE...AS SELECT... to make

UPDATE LIST when the corresponding row exists in another table

I need to update a field on a table to be true only if a matching row exists in another table, for all the rows where the column is currently null in the main table. This is a description of what I want to achieve: UPDATE [LenqReloaded].[dbo].[Enquir

count the table rows based on another table identifier

I'm trying to count my rows based on another table id DRIVER DB TABLE agent_id driver_id driver_name -------- ---------- ----------- 2 1 AAA 2 2 BBB 2 3 CCC 1 4 DDD I want to show the result (how many drivers are there based on agent_id). For example

select specific rows from the database in SQL

Possible Duplicate: How to select the nth row in a SQL database table? I have a table that I want to select Select 1 record and in other command I want to select second row and in other command 3th row and ... how can I select for example 4th record

Select the account field for rows that are linked to another table

I'm struggling with this issue from a long time and don't know how to solve it. It's hard for me to describe, so please be patient. There are two tables: Table "Users" UserId PK Gender Table "Forms" FormId PK UserId1 FK UserId2 FK Type

Select Data in SQL from No link between another table / column

Table and (columns) in question are: Attachment (att_id) Assignment (att_id, ctg_id, and itm_id) I have tried for several hours to try and call the data I am looking for but to avail. I can't figure out the logic behind it and it seems so simple. I n

How to insert rows retrieved to another table using ssis

I have a table and it has 500 rows. I want to retrieve only 10 rows and i want to insert into another table using control flow only. Through data flow task we can use OLEDB source and OLEDB destination. But i want result in such a way that by using e

Quick query to select all NOT records in another table in MySQL

I have a query that selects a table of nodes, then joins a table of titles to it. This is done by first joining an in-between table of node IDs and title IDs that allows a many-to-many relationship between the first two tables. Both joins are inner s

Select the rows of a MySQL table where the value is an array?

I have a table with a list of IDs. I use a query to select that and then fetch it as an array (I know how to do this). Then I want to select rows from another table where the IDs are in the array fetched earlier. How would I do this? Thanks in advanc

How to select MIN and MAX from another table

I have two tables in postgresql: ITEM (ID, TYPE) and ITEM_DATA (TS, ITEM_ID) in 1:n relation. The first table is small and the second is huge. I would like to know the best way to select min and max from the table ITEM_DATA for each row of a select f

Select a particular record from another table based on the status

I have two tables; its structure is given below In the first table I am assigning menu rights based on RoleID. In some cases I want to override some additional rights to a particular user (UserID). So this second table is using for additional rights

How can I collectively select 100 rows of 3 different tables?

I have 3 tables containing similar rows of data. I need to select 100 rows from all of the three tables with the following conditions: No more than 25 rows can be selected from Table A --> (name it count_a) No more than 40 rows can be selected from T

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK